﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace MasMembers.DAL
{
    public class JobEntity
    {
        public Int32 Id { get; set; }
        public Int32 Mem_Num { get; set; }
        public string Main_Job { get; set; }
        public string MJob_Type { get; set; }
        public string MJob_Geha { get; set; }
        public string MJob_Name { get; set; }
        public string MJob_Level { get; set; }
        public string Sec_Job { get; set; }
        public string SJob_Geha { get; set; }
        public Int32 Main_Salary { get; set; }
        public Int32 Addit_Salary { get; set; }
        public Int32 Income { get; set; }
        public string Working_Days { get; set; }
        public Int32 Working_Hours_From { get; set; }
        public Int32 Working_Hours_To { get; set; }
        public Int32 Daily_Working_Hours { get; set; }
        public Int32 Monthly_Working_Hours { get; set; }
        public bool IsGovern_Job { get; set; }
        public bool IsCurrent_Job { get; set; }
    }

    public class MJob
    {
        private static string ConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='MasMembers.accdb' ;Jet OLEDB:Database Password=Yarabe11";
        private static string CmdText = "";

        private static JobEntity FillEntityFromReader(OleDbDataReader reader)
        {
            JobEntity x = new JobEntity();
            x.Id = Int32.Parse(reader["Id"].ToString());
            if (reader["Mem_Num"] != null)
                x.Mem_Num = Convert.ToInt32(reader["Mem_Num"]);
            if (reader["Main_Job"] != null)
                x.Main_Job = reader["Main_Job"].ToString();
            if (reader["MJob_Type"] != null)
                x.MJob_Type = reader["MJob_Type"].ToString();
            if (reader["MJob_Geha"] != null)
                x.MJob_Geha = Convert.ToString(reader["MJob_Geha"]);
            if (reader["MJob_Name"] != null)
                x.MJob_Name = reader["MJob_Name"].ToString();
            if (reader["MJob_Level"] != null)
                x.MJob_Level = reader["MJob_Level"].ToString();
            if (reader["Sec_Job"] != null)
                x.Sec_Job = reader["Sec_Job"].ToString();
            if (reader["SJob_Geha"] != null)
                x.SJob_Geha = Convert.ToString(reader["SJob_Geha"]);
            if (reader["Main_Salary"] != null)
                x.Main_Salary = Convert.ToInt32(reader["Main_Salary"]);
            if (reader["Addit_Salary"] != null)
                x.Addit_Salary = Convert.ToInt32(reader["Addit_Salary"]);
            if (reader["Income"] != null)
                x.Income = Convert.ToInt32(reader["Income"]);
            if (reader["Working_Days"] != null)
                x.Working_Days = Convert.ToString(reader["Working_Days"]);
            if (reader["Working_Hours_From"] != null)
                x.Working_Hours_From = Convert.ToInt32(reader["Working_Hours_From"]);
            if (reader["Working_Hours_To"] != null)
                x.Working_Hours_To = Convert.ToInt32(reader["Working_Hours_To"]);
            if (reader["Daily_Working_Hours"] != null)
                x.Daily_Working_Hours = Convert.ToInt32(reader["Daily_Working_Hours"]);
            if (reader["Monthly_Working_Hours"] != null)
                x.Monthly_Working_Hours = Convert.ToInt32(reader["Monthly_Working_Hours"]);
            if (reader["IsCurrent_Job"] != null)
                x.IsCurrent_Job = Boolean.Parse(reader["IsCurrent_Job"].ToString());
            if (reader["IsGovern_Job"] != null)
                x.IsGovern_Job = Boolean.Parse(reader["IsGovern_Job"].ToString());
            


            return x;
        }

        public static List<String> getJobTypes() { 
            List<String> jobs = new List<String>();
            CmdText = "Select distinct MJob_Type from Job ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            try
            {
                Con.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["MJob_Type"] != null)
                        jobs.Add(reader["MJob_Type"].ToString());
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }
            return jobs;
        }
    
        public static List<JobEntity> GetAllJob()
        {
            JobEntity x;
            List<JobEntity> JobList = new List<JobEntity>();
            CmdText = "Select * from Job ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    x = FillEntityFromReader(reader);
                    JobList.Add(x);
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ عند تحديد البيانات المطلوبة من قاعدة البيانات ", "رساله ادارية " + ex.Message);
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return JobList;

        }

        public static JobEntity GetJobByMemId(Int32 Id)
        {
            JobEntity x = null;

            CmdText = "Select * from Job where Mem_Num = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Id", Id);

            try
            {
                Con.Open();

                OleDbDataReader reader = cmd.ExecuteReader();

                if (reader.Read())
                    x = FillEntityFromReader(reader);

                reader.Close();
            }
            catch (Exception ex)
            {
                            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                    Con.Close();
            }

            return x;
        }

        public static bool Add(JobEntity P)
        {
            bool flag = true;

            CmdText = "INSERT INTO job (Mem_Num,Main_Job,MJob_Type,MJob_Geha,MJob_Name,MJob_Level,Sec_Job,SJob_Geha,Main_Salary,Addit_Salary,Income,Working_Days,Working_Hours_From,Working_Hours_To,Daily_Working_Hours,Monthly_Working_Hours,IsCurrent_Job,IsGovern_Job) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Num", P.Mem_Num);
            cmd.Parameters.AddWithValue("@Main_Job", P.Main_Job);
            cmd.Parameters.AddWithValue("@MJob_Type", P.MJob_Type);
            cmd.Parameters.AddWithValue("@MJob_Geha", P.MJob_Geha);
            cmd.Parameters.AddWithValue("@MJob_Name", P.MJob_Name);
            cmd.Parameters.AddWithValue("@MJob_Level", P.MJob_Level);
            cmd.Parameters.AddWithValue("@Sec_Job", P.Sec_Job);
            cmd.Parameters.AddWithValue("@SJob_Geha", P.SJob_Geha);
            cmd.Parameters.AddWithValue("@Main_Salary", P.Main_Salary);
            cmd.Parameters.AddWithValue("@Addit_Salary", P.Addit_Salary);
            cmd.Parameters.AddWithValue("@Income", P.Income);
            cmd.Parameters.AddWithValue("@Working_Days", P.Working_Days);
            cmd.Parameters.AddWithValue("@Working_Hours_From", P.Working_Hours_From);
            cmd.Parameters.AddWithValue("@Working_Hours_To", P.Working_Hours_To);
            cmd.Parameters.AddWithValue("@Daily_Working_Hours", P.Daily_Working_Hours);
            cmd.Parameters.AddWithValue("@Monthly_Working_Hours", P.Monthly_Working_Hours);
            cmd.Parameters.AddWithValue("@IsCurrent_Job", P.IsCurrent_Job);
            cmd.Parameters.AddWithValue("@IsGovern_Job", P.IsGovern_Job);           

            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى ادخال البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

        public static bool Update(JobEntity P)
        {
            bool flag = true;
            CmdText = "Update job set Mem_Num = ? , Main_Job = ? , MJob_Type = ? , MJob_Geha = ? , MJob_Name = ? , MJob_Level = ? , Sec_Job = ? , SJob_Geha = ? , Main_Salary = ? , Addit_Salary = ? , Income= ? ,Working_Days = ? , Working_Hours_From = ? , Working_Hours_To = ? , Daily_Working_Hours = ? , Monthly_Working_Hours = ? , IsCurrent_Job = ? , IsGovern_Job=? Where Id=?";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_Num", P.Mem_Num);
            cmd.Parameters.AddWithValue("@Main_Job", P.Main_Job);
            cmd.Parameters.AddWithValue("@MJob_Type", P.MJob_Type);
            cmd.Parameters.AddWithValue("@MJob_Geha", P.MJob_Geha);
            cmd.Parameters.AddWithValue("@MJob_Name", P.MJob_Name);
            cmd.Parameters.AddWithValue("@MJob_Level", P.MJob_Level);
            cmd.Parameters.AddWithValue("@Sec_Job", P.Sec_Job);
            cmd.Parameters.AddWithValue("@SJob_Geha", P.SJob_Geha);
            cmd.Parameters.AddWithValue("@Main_Salary", P.Main_Salary);
            cmd.Parameters.AddWithValue("@Addit_Salary", P.Addit_Salary);
            cmd.Parameters.AddWithValue("@Income", P.Income);
            cmd.Parameters.AddWithValue("@Working_Days", P.Working_Days);
            cmd.Parameters.AddWithValue("@Working_Hours_From", P.Working_Hours_From);
            cmd.Parameters.AddWithValue("@Working_Hours_To", P.Working_Hours_To);
            cmd.Parameters.AddWithValue("@Daily_Working_Hours", P.Daily_Working_Hours);
            cmd.Parameters.AddWithValue("@Monthly_Working_Hours", P.Monthly_Working_Hours);
            cmd.Parameters.AddWithValue("@IsCurrent_Job", P.IsCurrent_Job);
            cmd.Parameters.AddWithValue("@IsGovern_Job", P.IsGovern_Job);   
            cmd.Parameters.AddWithValue("@Id", P.Id);

            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى تغيير البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }

            return flag;
        }

        public static bool Delete(int Mem_num)
        {
            bool flag = true;
            CmdText = "delete from Job where Mem_num = ? ";

            OleDbConnection Con = new OleDbConnection(ConnString);
            OleDbCommand cmd = new OleDbCommand(CmdText, Con);

            cmd.Parameters.AddWithValue("@Mem_num", Mem_num);
            try
            {
                Con.Open();
                Int32 NumOfRaws = cmd.ExecuteNonQuery();
                if (NumOfRaws == 0)
                    flag = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("خطأ فى حذف البيانات" + ex.Message);
                flag = false;
            }
            finally
            {
                if (Con.State == ConnectionState.Open)
                {
                    Con.Close();
                }
            }
            return flag;
        }

    }
}
